﻿using System;
using System.Data;
using System.Text;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace fanli.DAL
{
    /// <summary>
    /// 数据访问类fanli_order。
    /// </summary>
    public class fanli_order
    {
        iData.DataAcess worker = new iData.DataAcess();
        public fanli_order()
        { }

        #region  基本方法（系统生成）

        #region GetMaxId
        /// <summary>
        /// 得到最大ID
        /// </summary>
        public int GetMaxId()
        {
            string sql = "select max(id) from fanli_order";
            return worker.ExecuteCount(sql);
        }
        /// <summary>
        /// 得到最大ID (如不需要条件，可以设置strWhere为空，paraList为null )
        /// </summary>
        public int GetMaxId(string strWhere, List<SqlParameter> paraList)
        {
            string sql = "select max(id) from fanli_order";
            if (strWhere.Length > 0) sql += " where " + strWhere;
            return worker.ExecuteCount(sql);
        }

        #endregion

        #region Exists
        /// <summary>
        /// 是否存在该记录 (如不需要条件，可以设置strWhere为空，paraList为null )
        /// </summary>
        public bool Exists(string strWhere, List<SqlParameter> paraList)
        {
            string sql = "select count(*) from fanli_order";
            if (strWhere.Length > 0) sql += " where " + strWhere;
            int num = worker.ExecuteCount(sql, paraList);
            return num == 0 ? false : true;
        } 
        #endregion

        #region Add
        /// <summary>
        /// 增加一条数据,返回自增的id
        /// </summary>
        public int Add(fanli.Model.fanli_order model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into fanli_order(");
            strSql.Append("userid,shopID,status,lianmeng,fanliMoney,fanliStatus,trade_id,pay_time,commission_rate,commission,outer_code,num_iid,item_title,item_num,pay_price,real_pay_fee,category_id,category_name,shop_title,seller_nick,ext1,ext2)");
            strSql.Append(" values (");
            strSql.Append(" @userid, @shopID, @status, @lianmeng, @fanliMoney, @fanliStatus, @trade_id, @pay_time, @commission_rate, @commission, @outer_code, @num_iid, @item_title, @item_num, @pay_price, @real_pay_fee, @category_id, @category_name, @shop_title, @seller_nick, @ext1, @ext2)");
            //strSql.Append(";select @@IDENTITY");
            List<SqlParameter> list = new List<SqlParameter>();
			list.Add(new SqlParameter("@userid",model.userid ));
			list.Add(new SqlParameter("@shopID",model.shopID ));
			list.Add(new SqlParameter("@status",model.status ));
			list.Add(new SqlParameter("@lianmeng",model.lianmeng ));
			list.Add(new SqlParameter("@fanliMoney",model.fanliMoney ));
			list.Add(new SqlParameter("@fanliStatus",model.fanliStatus ));
			list.Add(new SqlParameter("@trade_id",model.trade_id ));
			list.Add(new SqlParameter("@pay_time",model.pay_time ));
			list.Add(new SqlParameter("@commission_rate",model.commission_rate ));
			list.Add(new SqlParameter("@commission",model.commission ));
			list.Add(new SqlParameter("@outer_code",model.outer_code ));
			list.Add(new SqlParameter("@num_iid",model.num_iid ));
			list.Add(new SqlParameter("@item_title",model.item_title ));
			list.Add(new SqlParameter("@item_num",model.item_num ));
			list.Add(new SqlParameter("@pay_price",model.pay_price ));
			list.Add(new SqlParameter("@real_pay_fee",model.real_pay_fee ));
			list.Add(new SqlParameter("@category_id",model.category_id ));
			list.Add(new SqlParameter("@category_name",model.category_name ));
			list.Add(new SqlParameter("@shop_title",model.shop_title ));
			list.Add(new SqlParameter("@seller_nick",model.seller_nick ));
			list.Add(new SqlParameter("@ext1",model.ext1 ));
			list.Add(new SqlParameter("@ext2",model.ext2 ));
            return worker.ExecuteInsert(strSql.ToString(), list);
        } 
        #endregion

        #region Update
        /// <summary>
        /// 更新一条数据,返回影响的行数（建议重载后使用）
        /// </summary>
        public int Update(fanli.Model.fanli_order model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update fanli_order set ");
            strSql.Append("userid=@userid,");
            strSql.Append("shopID=@shopID,");
            strSql.Append("status=@status,");
            strSql.Append("lianmeng=@lianmeng,");
            strSql.Append("fanliMoney=@fanliMoney,");
            strSql.Append("fanliStatus=@fanliStatus,");
            strSql.Append("trade_id=@trade_id,");
            strSql.Append("pay_time=@pay_time,");
            strSql.Append("commission_rate=@commission_rate,");
            strSql.Append("commission=@commission,");
            strSql.Append("outer_code=@outer_code,");
            strSql.Append("num_iid=@num_iid,");
            strSql.Append("item_title=@item_title,");
            strSql.Append("item_num=@item_num,");
            strSql.Append("pay_price=@pay_price,");
            strSql.Append("real_pay_fee=@real_pay_fee,");
            strSql.Append("category_id=@category_id,");
            strSql.Append("category_name=@category_name,");
            strSql.Append("shop_title=@shop_title,");
            strSql.Append("seller_nick=@seller_nick,");
            strSql.Append("ext1=@ext1,");
            strSql.Append("ext2=@ext2");
            strSql.Append(" where id=@id ");

            List<SqlParameter> list = new List<SqlParameter>();
            list.Add(new SqlParameter("@id", model.id));//id
            list.Add(new SqlParameter("@userid", model.userid));//用户id
            list.Add(new SqlParameter("@shopID", model.shopID));//商城id
            list.Add(new SqlParameter("@status", model.status));//订单状态
            list.Add(new SqlParameter("@lianmeng", model.lianmeng));//所属联盟
            list.Add(new SqlParameter("@fanliMoney", model.fanliMoney));//返利佣金
            list.Add(new SqlParameter("@fanliStatus", model.fanliStatus));//返利状态
            list.Add(new SqlParameter("@trade_id", model.trade_id));//订单号
            list.Add(new SqlParameter("@pay_time", model.pay_time));//确认时间
            list.Add(new SqlParameter("@commission_rate", model.commission_rate));//佣金比率
            list.Add(new SqlParameter("@commission", model.commission));//网站佣金
            list.Add(new SqlParameter("@outer_code", model.outer_code));//推广渠道
            list.Add(new SqlParameter("@num_iid", model.num_iid));//商品ID
            list.Add(new SqlParameter("@item_title", model.item_title));//商品名称
            list.Add(new SqlParameter("@item_num", model.item_num));//商品数量
            list.Add(new SqlParameter("@pay_price", model.pay_price));//成交价格
            list.Add(new SqlParameter("@real_pay_fee", model.real_pay_fee));//实际支付金额
            list.Add(new SqlParameter("@category_id", model.category_id));//商品类目ID
            list.Add(new SqlParameter("@category_name", model.category_name));//商品类目名称
            list.Add(new SqlParameter("@shop_title", model.shop_title));//店铺名称
            list.Add(new SqlParameter("@seller_nick", model.seller_nick));//卖家昵称
            list.Add(new SqlParameter("@ext1", model.ext1));//扩展1
            list.Add(new SqlParameter("@ext2", model.ext2));//扩展2

            return worker.ExecuteNonQuery(strSql.ToString(), list);
        }

        /// <summary>
        /// 执行sql语句并返回影响记录条数（请在扩展方法中写新函数，不建议直接调用）
        /// </summary>
        public int Update(string strUpdateSql, List<SqlParameter> cmdParms)
        {
            strUpdateSql = strUpdateSql.Replace("[tbl]", "fanli_order");
            return worker.ExecuteNonQuery(strUpdateSql, cmdParms);
        }

        #endregion

        #region Delete
        /// <summary>
        /// 删除一条数据
        /// </summary>
        public int Delete(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from fanli_order ");
            strSql.Append(" where id=@id ");
            List<SqlParameter> list = new List<SqlParameter>();
            list.Add(new SqlParameter("@id", id));

            return worker.ExecuteNonQuery(strSql.ToString(), list);
        }

        /// <summary>
        /// 根据where语句删除数据
        /// </summary>
        public int Delete(string strWhere, List<SqlParameter> paraList)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from fanli_order ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where ");
                strSql.Append(strWhere);
            }
            return worker.ExecuteNonQuery(strSql.ToString(), paraList);
        } 
        #endregion

        #region  获得一个字段值
        /// <summary>
        /// 获得数据列表(参数化)
        /// </summary>
        public string GetField(string fieldName, string strWhere, List<SqlParameter> cmdParms)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 ");
            strSql.Append(fieldName);
            strSql.Append(" FROM fanli_order ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return worker.ExecuteScalar(strSql.ToString(), cmdParms);
        }


        /// <summary>
        /// 获得记录条数count(*)
        /// </summary>
        public int GetCount(string strWhere, List<SqlParameter> cmdParms)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(*) ");
            strSql.Append(" FROM fanli_order ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return worker.ExecuteCount(strSql.ToString(), cmdParms);
        }
        #endregion

        #region  获得一条记录

        /// <summary>
        /// 用sql语句返回一个datarow（请在扩展方法中写新函数，不建议直接调用）
        /// </summary>
        public DataRow GetRowBySql(string strSelectSql, List<SqlParameter> cmdParams)
        {
            return worker.ExecuteOneRow(strSelectSql, cmdParams);
        }

        /// <summary>
        /// 获得一条记录
        /// </summary>
        public DataRow GetRow(string strWhere, List<SqlParameter> cmdParms,string dataFields)
        {
            StringBuilder strSql = new StringBuilder();
            if (dataFields == null|| dataFields=="")  dataFields = "id,userid,shopID,status,lianmeng,fanliMoney,fanliStatus,trade_id,pay_time,commission_rate,commission,outer_code,num_iid,item_title,item_num,pay_price,real_pay_fee,category_id,category_name,shop_title,seller_nick,ext1,ext2";
            strSql.Append("select top 1 ");
            strSql.Append(dataFields);
            strSql.Append(" FROM fanli_order ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return worker.ExecuteOneRow(strSql.ToString(), cmdParms);
        }

        /// <summary>
        /// 获得一条记录model
        /// </summary>
        public Model.fanli_order GetModel(string strWhere, List<SqlParameter> cmdParms)
        {
            DataRow dr = GetRow(strWhere, cmdParms ,"");
            return dr==null?null:DatarowToModel(dr);
        }

        /// <summary>
        /// 获得一条记录model
        /// </summary>
        public Model.fanli_order GetModel(int id)
        {
            List<SqlParameter> paraList = new List<SqlParameter>();
            paraList.Add(new SqlParameter("@id", id));
            DataRow dr = GetRow("id=@id", paraList ,"");
            return dr==null?null:DatarowToModel(dr);
        }
        #endregion

        #region  获得dataset列表
        /// <summary>
        /// 用sql语句返回一个dataset
        /// </summary>
        /// <param name="strSelectSql">查询sql语句</param>
        public DataSet GetDatasetBySql(string strSelectSql)
        {
            return worker.Execute(strSelectSql);
        }

        /// <summary>
        /// 用sql语句返回一个dataset
        /// </summary>
        /// <param name="strSelectSql">查询sql语句</param>
        /// <param name="cmdParms">输入参数列表</param>
        public DataSet GetDatasetBySql(string strSelectSql, List<SqlParameter> cmdParms)
        {
            return worker.Execute(strSelectSql, cmdParms);
        }


        /// <summary>
        /// 根据页码获取当前一页的数据
        /// </summary>
        /// <param name="currentPage">页码</param>
        /// <param name="pageSize">每页的记录条数</param>
        /// <param name="strWhere">查询子句，不包括order by</param>
        /// <param name="strOrderBy">查询order by子句</param>
        /// <param name="cmdParms">参数列表</param>
        /// <param name="dataFields">要查询的字段列表，为空表示所有字段</param>
        /// <param name="tblName">表名，支持多表</param>
        public DataSet GetDatasetByPageSql(int currentPage, int pageSize, string strWhere, string strOrderBy, List<SqlParameter> cmdParms, string dataFields, string tblName)
        {
            return worker.ExecutePageSql(currentPage, pageSize, strWhere, strOrderBy, cmdParms, dataFields, tblName);
        }
        /// <summary>
        /// 根据页码获取当前一页的数据
        /// </summary>
        /// <param name="currentPage">页码</param>
        /// <param name="pageSize">每页的记录条数</param>
        /// <param name="strWhere">查询子句，不包括order by</param>
        /// <param name="strOrderBy">查询order by子句</param>
        /// <param name="cmdParms">参数列表</param>
        /// <param name="dataFields">要查询的字段列表，为空表示所有字段</param>
        public DataSet GetDatasetByPageSql(int currentPage, int pageSize, string strWhere, string strOrderBy, List<SqlParameter> cmdParms, string dataFields)
        {
            return GetDatasetByPageSql(currentPage, pageSize, strWhere, strOrderBy, cmdParms, dataFields, "fanli_order");
        }

        /// <summary>
        /// 获得dataset列表
        /// </summary>
        /// <param name="strWhere">查询条件，可带order by语句</param>
        public DataSet GetDataset(string strWhere)
        {
            return GetDataset(strWhere, null, "");
        }
        
        /// <summary>
        /// 获得dataset列表
        /// </summary>
        /// <param name="strWhere">查询条件，可带order by语句</param>
        /// <param name="cmdParms">输入参数列表</param>
        /// <param name="dataFields">获取的字段列表，传递空则表示所有</param>
        public DataSet GetDataset(string strWhere, List<SqlParameter> cmdParms,string dataFields)
        {
            StringBuilder strSql = new StringBuilder();
            if (dataFields == "") dataFields = "id,userid,shopID,status,lianmeng,fanliMoney,fanliStatus,trade_id,pay_time,commission_rate,commission,outer_code,num_iid,item_title,item_num,pay_price,real_pay_fee,category_id,category_name,shop_title,seller_nick,ext1,ext2";
            strSql.Append("select ");
            strSql.Append(dataFields);
            strSql.Append(" FROM fanli_order ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return worker.Execute(strSql.ToString(), cmdParms);
        }
        
        /// <summary>
        /// 获得dataset列表
        /// </summary>
        /// <param name="strWhere">查询条件，可带order by语句</param>
        /// <param name="cmdParms">输入参数列表</param>
        /// <param name="dataFields">获取的字段列表，传递空则表示所有</param>
        /// <param name="topN">获取的条数</param>
        public DataSet GetDataset(string strWhere, List<SqlParameter> cmdParms,string dataFields,int topN)
        {
            StringBuilder strSql = new StringBuilder();
            if (dataFields == "") dataFields = "id,userid,shopID,status,lianmeng,fanliMoney,fanliStatus,trade_id,pay_time,commission_rate,commission,outer_code,num_iid,item_title,item_num,pay_price,real_pay_fee,category_id,category_name,shop_title,seller_nick,ext1,ext2";
            strSql.Append("select ");
            strSql.Append(dataFields);
            strSql.Append(" FROM fanli_order ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return worker.ExecuteTop(strSql.ToString(), cmdParms, topN);
        }

        /// <summary>
        /// 获得modellist
        /// </summary>
        /// <param name="strWhere">查询条件，可带order by语句</param>
        public List<Model.fanli_order> GetModelList(string strWhere)
        {
            DataSet dset = GetDataset(strWhere, null, "");
            if (dset == null) return null;
            else  return DatatableToModelList(dset.Tables[0]);
        }

        /// <summary>
        /// 获得modellist
        /// </summary>
        /// <param name="strWhere">查询条件，可带order by语句</param>
        /// <param name="cmdParms">输入参数列表</param>
        /// <param name="dataFields">获取的字段列表，传递空则表示所有</param>
        public List<Model.fanli_order> GetModelList(string strWhere, List<SqlParameter> cmdParms, string dataFields)
        {
            DataSet dset = GetDataset(strWhere, cmdParms, dataFields);
            if (dset == null) return null;
            else  return DatatableToModelList(dset.Tables[0]);
        }

        /// <summary>
        /// 获得modellist
        /// </summary>
        /// <param name="strWhere">查询条件，可带order by语句</param>
        /// <param name="cmdParms">输入参数列表</param>
        /// <param name="dataFields">获取的字段列表，传递空则表示所有</param>
        /// <param name="topN">获取的条数</param>
        public List<Model.fanli_order> GetModelList(string strWhere, List<SqlParameter> cmdParms, string dataFields, int topN)
        {
            DataSet dset = GetDataset(strWhere, cmdParms, dataFields, topN);
            if (dset == null) return null;
            else return DatatableToModelList(dset.Tables[0]);
        }
        #endregion

        #region  change to model
        /// <summary>
        /// 把数据从datatable转存成modellist
        /// </summary>
        public List<Model.fanli_order> DatatableToModelList(DataTable dt)
        {
            List<Model.fanli_order> modelList = new List<Model.fanli_order>();
            int rowsCount = dt.Rows.Count;
            if (rowsCount > 0)
            {
                for (int n = 0; n < rowsCount; n++)
                {
                    Model.fanli_order model = new Model.fanli_order();
                    model = DatarowToModel(dt.Rows[n]);
                    modelList.Add(model);
                }
            }
            return modelList;
        }
        /// <summary>
        /// 把数据从datarow转存成model
        /// </summary>
        public fanli.Model.fanli_order DatarowToModel(DataRow dr)
        {
            Model.fanli_order model = new Model.fanli_order();
            model.id = dr["id"] == DBNull.Value ? 0 : int.Parse(dr["id"].ToString());//id
            model.userid = dr["userid"] == DBNull.Value ? 0 : int.Parse(dr["userid"].ToString());//用户id
            model.shopID = dr["shopID"] == DBNull.Value ? 0 : int.Parse(dr["shopID"].ToString());//商城id
            model.status = dr["status"] == DBNull.Value ? 0 : int.Parse(dr["status"].ToString());//订单状态
            model.lianmeng = dr["lianmeng"].ToString();//所属联盟
            model.fanliMoney = dr["fanliMoney"] == DBNull.Value ? 0 : float.Parse(dr["fanliMoney"].ToString());//返利佣金
            model.fanliStatus = dr["fanliStatus"] == DBNull.Value ? 0 : int.Parse(dr["fanliStatus"].ToString());//返利状态
            model.trade_id = dr["trade_id"].ToString();//订单号
            model.pay_time = dr["pay_time"] == DBNull.Value ? DateTime.Parse("2000-1-1") : DateTime.Parse(dr["pay_time"].ToString());//确认时间
            model.commission_rate = dr["commission_rate"] == DBNull.Value ? 0 : float.Parse(dr["commission_rate"].ToString());//佣金比率
            model.commission = dr["commission"] == DBNull.Value ? 0 : float.Parse(dr["commission"].ToString());//网站佣金
            model.outer_code = dr["outer_code"].ToString();//推广渠道
            model.num_iid = dr["num_iid"] == DBNull.Value ? 0 : int.Parse(dr["num_iid"].ToString());//商品ID
            model.item_title = dr["item_title"].ToString();//商品名称
            model.item_num = dr["item_num"] == DBNull.Value ? 0 : int.Parse(dr["item_num"].ToString());//商品数量
            model.pay_price = dr["pay_price"] == DBNull.Value ? 0 : float.Parse(dr["pay_price"].ToString());//成交价格
            model.real_pay_fee = dr["real_pay_fee"] == DBNull.Value ? 0 : float.Parse(dr["real_pay_fee"].ToString());//实际支付金额
            model.category_id = dr["category_id"] == DBNull.Value ? 0 : int.Parse(dr["category_id"].ToString());//商品类目ID
            model.category_name = dr["category_name"].ToString();//商品类目名称
            model.shop_title = dr["shop_title"].ToString();//店铺名称
            model.seller_nick = dr["seller_nick"].ToString();//卖家昵称
            model.ext1 = dr["ext1"].ToString();//扩展1
            model.ext2 = dr["ext2"].ToString();//扩展2
            return model;
        }


        #endregion

        #endregion

        #region 自定义方法（非系统生成的方法，请在此区域单独写函数，以便维护）

        /// <summary>
        /// 函数功能：示例函数
        /// 作者：buzhizhe 创建时间：2011.6.22
        /// 修改者：buzhizhe 最后修改时间：2011.6.22
        /// </summary>
        public void YourFunction()
        {
            ///
        } 


        #endregion
    }
}



